<?php
if(!isset($_REQUEST['cid']) || !isset($_REQUEST['path'])) die('Invalid usage - missing param');
$cid = $_REQUEST['cid'];
$path = $_REQUEST['path'];

define('CID', $cid);
define('READ_ONLY_SESSION',true);
require_once('../../../include.php');
ModuleManager::load_modules();

$start_date = Module::static_get_module_variable($path, 'start_date', null);
$start_date = date('Y-m-d', strtotime($start_date));
$end_date = Module::static_get_module_variable($path, 'end_date', null);
$end_date = date('Y-m-d', strtotime($end_date));
$employee = Module::static_get_module_variable($path,'employee', null);
$maintenance_group = Module::static_get_module_variable($path,'maintenance_group', null);
if($employee == null)
{
	$employee_is_null = true;
	$employee=0;
}
else
{
	$employee_is_null = false;
}
			
$manager =  CRM_ContactsCommon::get_my_record();
$is_area_manager = array_key_exists('area manager', $manager['access']);

if(!$is_area_manager){
    $quyettoan = Module::static_get_module_variable($path,'quyettoan', null);
    if($quyettoan == null){
    }
    else{
        $is_area_manager = true;
        $manager = CRM_ContactsCommon::get_contact($quyettoan);
    }
}

$sql = "
				select distinct sctv_material_data_1.id, sctv_material_data_1.f_excel_ordinary_ordinal excel_ordinary_ordinal, sctv_material_data_1.f_excel_free_ordinal excel_free_ordinal, 
				sctv_maintenanceacceptancematerial_data_1.f_material_type material_type, 
				sctv_material_data_1.f_material_code material_code, sctv_material_data_1.f_material_name material_name
				from sctv_material_data_1 
				left join sctv_maintenanceacceptancematerial_data_1 on sctv_maintenanceacceptancematerial_data_1.f_material = sctv_material_data_1.id and
					(sctv_material_data_1.f_excel_ordinary_ordinal = 0 or sctv_material_data_1.f_excel_free_ordinal = 0) and
					(sctv_maintenanceacceptancematerial_data_1.f_material_type <= 1) and
					sctv_maintenanceacceptancematerial_data_1.f_maintenanceacceptance in
						(
							select id
							from sctv_maintenanceacceptance_data_1
							where sctv_maintenanceacceptance_data_1.active = 1 and
								(%b = false or sctv_maintenanceacceptance_data_1.created_by = %d) and
								f_maintenance in
								(
									select id
									from sctv_maintenance_data_1
									where sctv_maintenance_data_1.active = 1 and sctv_maintenance_data_1.f_status = '1' and
										(%b = true or sctv_maintenance_data_1.f_deployment_employee = %d) and
										sctv_maintenance_data_1.f_acceptance_date between %D and %D
								) 
						)   
				where
					(sctv_material_data_1.f_excel_ordinary_ordinal > 0 or sctv_material_data_1.f_excel_free_ordinal > 0) 
					or
					(
						sctv_maintenanceacceptancematerial_data_1.active = 1 and
					  	sctv_maintenanceacceptancematerial_data_1.f_material_type <= 1 and
						f_maintenanceacceptance in
						(
							select id
							from sctv_maintenanceacceptance_data_1
							where sctv_maintenanceacceptance_data_1.active = 1 and
							(%b = false or sctv_maintenanceacceptance_data_1.created_by = %d) and
								f_maintenance in
								(
									select id
									from sctv_maintenance_data_1
									where sctv_maintenance_data_1.active = 1 and sctv_maintenance_data_1.f_status = '1' and
										(%b = true or sctv_maintenance_data_1.f_deployment_employee = %d) and
										sctv_maintenance_data_1.f_acceptance_date between %D and %D
								) 
						)
					)
				order by sctv_material_data_1.f_excel_ordinary_ordinal desc, sctv_material_data_1.f_excel_free_ordinal desc
			";
																			
			
			$records = DB::GetAll($sql, array($is_area_manager, $manager['login'], $employee_is_null, $employee, $start_date, $end_date, $is_area_manager, $manager['login'], $employee_is_null, $employee, $start_date, $end_date));
			
			$columns_index = array();
			$columns = array(
				array('name'=>__('STT'), 'width'=>'8'),
				array('name'=>__('Receipt'), 'width'=>'10'),
				array('name'=>__('Maintenance Code'), 'width'=>'10'),
				//array('name'=>__('Cable Contract Code'), 'width'=>'10'),
				//array('name'=>__('Internet Contract Code'), 'width'=>'10'),
				array('name'=>__('Customer Code'), 'width'=>'10'),
				array('name'=>__('Customer Name'), 'width'=>'30'),
				array('name'=>__('Address'), 'width'=>'40'),				
				array('name'=>__('Maintenance Date'), 'width'=>'15'),	
				array('name'=>__('Acceptance Date'), 'width'=>'15'),				
				array('name'=>__('Số tiền'), 'width'=>'20'),
				//array('name'=>__('Phí hòa mạng'), 'width'=>'20'),
				//array('name'=>__('Bán đầu số'), 'width'=>'20'),
				array('name'=>__('Bán vật tư'), 'width'=>'20'),
				array('name'=>__('Ký quỹ'), 'width'=>'20'),
			);
			$index = count($columns);
			
			foreach ($records as $record) {
				if($record['excel_ordinary_ordinal'] > 0)
				{
					$price_istvbox = SCTV_MaterialCommon::get_current_price_istvbox($record['id'], $end_date);
					$columns[] = array('name'=>$record['material_name'] . ' ' . $record['material_code'] . ' ' . str_replace('&nbsp;', ' ', Utils_CurrencyFieldCommon::format($price_istvbox['price'])), 'width'=>'15', 'nowrap'=>'0');	
															
					$columns_index += array($record['id'] . '_0'=>$index);
				
					$index++;
				}			
			}
			
			foreach ($records as $record) {
				if($record['excel_free_ordinal'] > 0)
				{
					$columns[] = array('name'=>$record['material_name'] . ' ' . $record['material_code'], 'width'=>'15', 'nowrap'=>'0');
						
					$columns_index += array($record['id'] . '_1'=>$index);
				
					$index++;
				}
			}
			
			$midle_index = $index;
			//$columns[] = array('name'=>__('Gói cước'), 'width'=>'20');$index++;
			//$columns[] = array('name'=>__('Nghiệp vụ'), 'width'=>'30');$index++;
			$columns[] = array('name'=>__('Ghi chú'), 'width'=>'30');$index++;
			$columns[] = array('name'=>__('Người bảo trì'), 'width'=>'30');$index++;
			$columns[] = array('name'=>__('Nhân viên quyết toán'), 'width'=>'30');$index++;
			
			// Vat tu khac
			foreach ($records as $record) {
				if($record['excel_ordinary_ordinal'] == 0 && $record['material_type'] == 0)
				{
					$price_istvbox = SCTV_MaterialCommon::get_current_price_istvbox($record['id'], $end_date);
					$columns[] = array('name'=>$record['material_name'] . ' ' . $record['material_code'] . ' ' . str_replace('&nbsp;', ' ', Utils_CurrencyFieldCommon::format($price_istvbox['price'])), 'width'=>'15', 'nowrap'=>'0');	
				
					$columns_index += array($record['id'] . '_' . $record['material_type']=>$index);
				
					$index++;
				}
				
				if($record['excel_free_ordinal'] == 0 && $record['material_type'] == 1)
				{
					$columns[] = array('name'=>$record['material_name'] . ' ' . $record['material_code'], 'width'=>'15', 'nowrap'=>'0');										
					$columns_index += array($record['id'] . '_' . $record['material_type']=>$index);				
					$index++;
				}
			}
			
			$default_row[] = array();
			for($i = 0; $i < count($columns)-1; $i++)
				$default_row[] = '';			
			
			require_once 'modules/Libs/PHPExcel/lib/PHPExcel.php';
			require_once 'modules/Libs/PHPExcel/lib/PHPExcel/Writer/Excel5.php';
		
			$objPHPExcel = new PHPExcel();
					
			$sheet = $objPHPExcel->getActiveSheet();
			
			//for($i = 0; $i <= )
				//$sheet->mergeCellsByColumnAndRow($index, $row_number, $index, $row_number);
			//}
		
			$row_number = 0;$column = 0;	
			$row_number++;
			foreach ($columns as $key=>$value){
				$sheet->getColumnDimensionByColumn($column)->setWidth($value['width']);
			
				$sheet->setCellValueByColumnAndRow($column, $row_number, $value['name']);
			
				$column++;
			}
								
			$crists = array('status'=>1, '>=acceptance_date'=>$start_date, '<=acceptance_date'=>$end_date);
			if(!$employee_is_null)
			 	$crists += array('deployment_employee'=>$employee);
			if($maintenance_group != null)
				$crists += array('maintenance_group'=>$maintenance_group);
			$maintenances = Utils_RecordBrowserCommon::get_records('SCTV_Maintenance', $crists, array(), array('acceptance_date'=>'ASC'));
			$stt = 1;
			foreach ($maintenances as $maintenance_id => $maintenance)
			{
				$acceptances = Utils_RecordBrowserCommon::get_records('SCTV_MaintenanceAcceptance', array('created_by'=>$manager['login'], 'maintenance'=>$maintenance_id));
				foreach ($acceptances as $acceptance_id => $acceptance)
				{
					if($acceptance['created_by'] != $manager['login'] && $is_area_manager) continue;																
					$materials = Utils_RecordBrowserCommon::get_records('SCTV_MaintenanceAcceptanceMaterial', array('maintenanceacceptance'=>$acceptance_id, '<=material_type'=>1), array());					
					$row = $default_row;
					$row[0] =  $stt;$stt++;
					$row[1] =  $acceptance['receipt'];
					$row[2] = $maintenance['maintenance_code'];
					//$row[3] = $maintenance['cable_contract_code'];
					//$row[4] = $maintenance['internet_contract_code'];
					$row[3] = $maintenance['customer_code'];
					$row[4] = $acceptance['customer_name'];
					$row[5] = $acceptance['address'];
					$row[6] = date('d-m-Y', strtotime($maintenance['maintenance_date']));
					$row[7] = date('d-m-Y', strtotime($maintenance['acceptance_date']));
					//$row[10] = 999;
					//$fee = $acceptance['fee'];				
					//$row[11] = $fee;
					///$row[12] = 999;
					//$row[13] = 999;
					$deposit = $acceptance['deposit'];
					$row[10] = $deposit;
					
					$summary_tvbox = 0;
					$summary_material = 0;
					foreach ($materials as $acceptance_material_id => $acceptance_material)
					{
						if($acceptance_material['material_type'] == 1)
						{
							$row[$columns_index[$acceptance_material['material'] . '_' . $acceptance_material['material_type']]] = $acceptance_material['amount'];	
						}
						else $row[$columns_index[$acceptance_material['material'] . '_' . $acceptance_material['material_type']]] = $acceptance_material['amount'];
						
						if($acceptance_material['material_type'] == 0)
						{
							$istvbox = Utils_RecordBrowserCommon::get_value('SCTV_Material', $acceptance_material['material'], 'istvbox');
							if($istvbox == 1)
							{
								$summary_tvbox += $acceptance_material['total'];
							}
							else 
							{
								$summary_material += $acceptance_material['total'];
							}
							
						}
					}
					
					$row[8] = $deposit + $summary_tvbox + $summary_material;
					//$row[12] = $summary_tvbox;
					$row[9] = $summary_material;
																																	
					$new_index = $midle_index;
					/*if(empty($maintenance['deployment_bundle']))
						$row[$new_index] = null;
					else
						$row[$new_index] = Utils_RecordBrowserCommon::get_value('SCTV_Bundle', $maintenance['deployment_bundle'], 'name');
					$new_index++;
					$row[$new_index] = $maintenance['deployment_business'];					
					$new_index++;*/
					$row[$new_index] = $acceptance['note'];					
					$new_index++;
					$row[$new_index] = CRM_ContactsCommon::contact_format_no_company($maintenance['deployment_employee'], true);
					$new_index++;
					$contact =  CRM_ContactsCommon::get_contact_by_user_id($acceptance['created_by']);
					$row[$new_index] = CRM_ContactsCommon::contact_format_no_company($contact, true);
					$new_index++;
					
					$row_number++;
					$column = 0;
		
					foreach ($row as $value){			
						$sheet->setCellValueByColumnAndRow($column, $row_number, $value);			
						$column++;
					}
				}
			}

			$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
		
		header('Content-Encoding: UTF-8');
		header('Content-type: text/xls; charset=UTF-8');
		header('Content-disposition: attachement; filename="'. $report .date('Y_m_d__H_i_s').'.xls"');
		$objWriter->save('php://output');
?>
